package com.million.project.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.million.project.pojo.ApplyRoads;
import com.million.project.pojo.AuditLogsPage;
import com.million.project.pojo.RoadApplyLogs;
import com.million.project.pojo.RoadApplySum;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

@Mapper
public interface RoadApplyLogsMapper extends BaseMapper<RoadApplyLogs> {

  /**
   * 审核通过或不通过都会更改审核状态
   */
  @Update("UPDATE tbl_road_apply_logs set audit_status = 1 "
      + "where road_apply_logs_id = #{roadApplyLogsId}")
  int checkRoads(Integer roadApplyLogsId);

  /**
   * 得到所有分页查询信息
   */
  @Select(
      "select road_apply_logs_id,real_name,tbl_users.username,id_card,road_begin,road_end,"
          + "apply_times,cargo_type,begin_date,end_date,apply_date,"
          + "pass_cities,"
          + "init_road,cargo_weight,suggest_road,whether_use_advice,"
          + "(cargo_weight+truck_weight) AS total_weight,truck_axes,"
          + "truck_tyres,current_condition,paths from tbl_road_apply_logs,\n"
          + "        tbl_users,tbl_trucks where tbl_users.username="
          + "tbl_road_apply_logs.username and "
          + "tbl_road_apply_logs.truck_license="
          + "tbl_trucks.truck_license and audit_status=0")
//  @Select(
//          "select road_apply_logs_id,real_name,tbl_users.username,id_card,"
//                  + "apply_times,cargo_type,begin_date,end_date,apply_date,"
//                  + "pass_cities,"
//                  + "init_road,cargo_weight,suggest_road,whether_use_advice,"
//                  + "(cargo_weight+truck_weight) AS total_weight,truck_axes,"
//                  + "truck_tyres,current_condition from tbl_road_apply_logs,\n"
//                  + "        tbl_users,tbl_trucks where tbl_users.username="
//                  + "tbl_road_apply_logs.username and "
//                  + "tbl_road_apply_logs.truck_license="
//                  + "tbl_trucks.truck_license and audit_status=0")
  List<ApplyRoads> selectAllPage();

  /**
   * 查询历史分页信息
   */
  @Select(
      "select audit_logs_id,auditor_username,audit_date,road_begin,road_end,"
          + "(select real_name from tbl_users where username=#{username} )\n"
          + "AS auditorRealName,whether_pass,whether_pass_reason,\n"
          + "t3.real_name,\n"
          + "tbl_road_apply_logs.username,tbl_users.id_card,apply_times,"
          + "cargo_type,end_date,begin_date,apply_date,pass_cities,init_road,"
          + "suggest_road,whether_use_advice,cargo_weight,"
          + "(cargo_weight+truck_weight) AS total_weight,truck_axes,\n"
          + "    truck_tyres,current_condition\n"
          + "from tbl_audit_logs,tbl_road_apply_logs,tbl_users,tbl_trucks,\n"
          + "(select DISTINCT real_name from tbl_users,(select username from "
          + "tbl_road_apply_logs,(select road_apply_id\n"
          + "from tbl_audit_logs where auditor_username=#{username})AS t where"
          + " road_apply_logs_id=t.road_apply_id) AS t2\n"
          + "where t2.username=tbl_users.username) AS t3\n"
          + "where auditor_username=#{username} and "
          + "tbl_audit_logs.road_apply_id=\n"
          + "tbl_road_apply_logs.road_apply_logs_id and tbl_users.username="
          + "tbl_road_apply_logs.username and "
          + "tbl_road_apply_logs.truck_license=tbl_trucks.truck_license\n"
          + "and t3.real_name=tbl_users.real_name and "
          + "tbl_audit_logs.deleted=0;")
  List<AuditLogsPage> selectAllPageLogs(String username);

  @Select(
      "select audit_logs_id,auditor_username,audit_date,road_begin,road_end,"
          + "(select real_name from tbl_users where username=#{username} )\n"
          + "AS auditorRealName,whether_pass,whether_pass_reason,\n"
          + "t3.real_name,\n"
          + "tbl_road_apply_logs.username,tbl_users.id_card,apply_times,"
          + "cargo_type,end_date\n"
          + ",begin_date,apply_date,pass_cities,init_road,suggest_road,"
          + "whether_use_advice,cargo_weight,(cargo_weight+truck_weight) "
          + "AS total_weight,truck_axes,\n"
          + "    truck_tyres,current_condition\n"
          + "from tbl_audit_logs,tbl_road_apply_logs,tbl_users,tbl_trucks,\n"
          + "(select DISTINCT real_name from tbl_users,(select username "
          + "from tbl_road_apply_logs,(select road_apply_id\n"
          + "from tbl_audit_logs where auditor_username=#{username})"
          + "AS t where road_apply_logs_id=t.road_apply_id) AS t2\n"
          + "where t2.username=tbl_users.username) AS t3\n"
          + "where auditor_username=#{username} and "
          + "tbl_audit_logs.road_apply_id=\n"
          + "tbl_road_apply_logs.road_apply_logs_id and "
          + "tbl_users.username=tbl_road_apply_logs.username and "
          + "tbl_road_apply_logs.truck_license=tbl_trucks.truck_license\n"
          + "and t3.real_name=tbl_users.real_name and "
          + "tbl_audit_logs.whether_pass=1 and tbl_audit_logs.deleted=0 \n"
          + ";")
  List<AuditLogsPage> selectPassPageLogs(String username);

  @Select(
          "select audit_logs_id,auditor_username,audit_date,road_begin,road_end,"
                  + "(select real_name from tbl_users where username=#{username} )\n"
                  + "AS auditorRealName,whether_pass,whether_pass_reason,\n"
                  + "t3.real_name,\n"
                  + "tbl_road_apply_logs.username,tbl_users.id_card,apply_times,"
                  + "cargo_type,end_date\n"
                  + ",begin_date,apply_date,pass_cities,init_road,suggest_road,"
                  + "whether_use_advice,cargo_weight,(cargo_weight+truck_weight) "
                  + "AS total_weight,truck_axes,\n"
                  + "    truck_tyres,current_condition\n"
                  + "from tbl_audit_logs,tbl_road_apply_logs,tbl_users,tbl_trucks,\n"
                  + "(select DISTINCT real_name from tbl_users,(select username "
                  + "from tbl_road_apply_logs,(select road_apply_id\n"
                  + "from tbl_audit_logs where auditor_username=#{username})"
                  + "AS t where road_apply_logs_id=t.road_apply_id) AS t2\n"
                  + "where t2.username=tbl_users.username) AS t3\n"
                  + "where auditor_username=#{username} and "
                  + "tbl_audit_logs.road_apply_id=\n"
                  + "tbl_road_apply_logs.road_apply_logs_id and "
                  + "tbl_users.username=tbl_road_apply_logs.username and "
                  + "tbl_road_apply_logs.truck_license=tbl_trucks.truck_license\n"
                  + "and t3.real_name=tbl_users.real_name and "
                  + "tbl_audit_logs.whether_pass=0 and tbl_audit_logs.deleted=0 \n"
                  + ";")
  List<AuditLogsPage> selectNotPassPageLogs(String username);


  int deleteByPrimaryKey(Integer roadApplyLogsId);


  int insert(RoadApplyLogs record);


  RoadApplyLogs selectByPrimaryKey(Integer roadApplyLogsId);


  List<RoadApplyLogs> selectAll();


  int updateByPrimaryKey(RoadApplyLogs record);

  @Update("UPDATE tbl_road_apply_logs set audit_status = 2 where "
      + "road_apply_logs_id " +
      "= #{roadApplyLogsId}" +
      " and username = #{username} ")
  int updateAuditStatus(Integer roadApplyLogsId, String username);

  @Select("SELECT * from tbl_road_apply_logs where username = #{username} and "
      + " audit_status = 1 and deleted = 0 order by apply_date ASC  ")
  List<RoadApplyLogs> selectHistoryPage(String username);

  @Update("UPDATE tbl_road_apply_logs SET deleted = 1 where road_apply_logs_id "
      + "= #{roadApplyLogsId}")
  int updateHistoryLogById(Integer roadApplyLogsId);

  @Select("Select * from tbl_road_apply_logs where username = #{username} "
      + "and audit_status = 0 ")
  List<RoadApplyLogs> getAllApplyLogs(String username);

  int selectApplyNum();

  int selectPassApplyNum();

  @Select(" SELECT MAX(road_apply_logs_id) from tbl_road_apply_logs")
  int selectLastInsertId();

  List<RoadApplyLogs> selectBetweenDate(String datef, String dateb);

  int selectNumBetweenDate(String datef, String dateb);

  List<RoadApplySum> selectByPopular(String datef, String dateb);

//  int insertAILogs(RoadApplyLogs roadApplyLogs);

  @Select("select count(1) from tbl_road_apply_logs where username=#{username} AND audit_status<>2;")
  int getApplyTimes(@Param("username") String username);
}